import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
# from fastapi import Request
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

check_head_router = APIRouter(prefix="/checkHead", dependencies=[Depends(oauth2_scheme)], tags=["耗材盘点"])


class CheckHeadSearch(PaginationBase):
    sort: str = Field(default=None, description="排序字段")
    order: str = Field(default="desc", description="正序或者倒叙")
    fsiteNo: str = Field(None, description="站点编号")
    ftakedateStart: str = Field(None, description="开始时间")
    ftakedateEnd: str = Field(None, description="结束时间")
    fbilltype: int = Field(None, description="单据类型")
    fpduser: int = Field(None, description="盘点用户ID")
    fstockno: int = Field(None, description="仓库编号")
    fbillno: int = Field(None, description="物料编号")
    fbillstate: int = Field(None, description="单据状态")


@check_head_router.get("/list", summary="耗材盘点查询")
def get_check_head(head: CheckHeadSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.m_tc_check_head where fbilltype=2 "
    if head.fsiteNo is not None:
        sql = sql + " and fsite_no = :fsiteNo"
        param["fsiteNo"] = head.fsiteNo
    if head.fbillno is not None:
        sql = sql + " and fbillno like concat('%',:fbillno,'%')"
        param["fbillno"] = head.fbillno
    if head.fpduser is not None:
        sql = sql + " and fpduser = :fpduser"
        param["fpduser"] = head.fpduser
    if head.fstockno is not None:
        sql = sql + " and fstockno = :fstockno"
        param["fstockno"] = head.fstockno
    if head.fbilltype is not None:
        sql = sql + " and fbilltype = :fbilltype"
        param["fbilltype"] = head.fbilltype
    if head.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate"
        param["fbillstate"] = head.fbillstate
    if (head.ftakedateStart is not None) and (head.ftakedateEnd is not None):
        sql = sql + " and to_char(fpddate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd"
        param["ftakedateStart"] = head.ftakedateStart
        param["ftakedateEnd"] = head.ftakedateEnd
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fsite_no,fpduser,fpddate,fstockno,fitemcount,fpiececount,ftotalamt,"
    sql = sql + " fexplanation,fupddate,foperator,fcheckdate,fcheckuser,fbillstate,fbilltype"
    sql = sql + " from lims.m_tc_check_head where fbilltype=2 "
    if head.fsiteNo is not None:
        sql = sql + " and fsite_no = :fsiteNo"
        param["fsiteNo"] = head.fsiteNo
    if head.fbillno is not None:
        sql = sql + " and fbillno like concat('%',:fbillno,'%')"
        param["fbillno"] = head.fbillno
    if head.fpduser is not None:
        sql = sql + " and fpduser = :fpduser"
        param["fpduser"] = head.fpduser
    if head.fstockno is not None:
        sql = sql + " and fstockno = :fstockno"
        param["fstockno"] = head.fstockno
    if head.fbilltype is not None:
        sql = sql + " and fbilltype = :fbilltype"
        param["fbilltype"] = head.fbilltype
    if head.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate"
        param["fbillstate"] = head.fbillstate
    if (head.ftakedateStart is not None) and (head.ftakedateEnd is not None):
        sql = sql + " and to_char(fpddate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd"
        param["ftakedateStart"] = head.ftakedateStart
        param["ftakedateEnd"] = head.ftakedateEnd
    sql = sql + " order by fpddate,fbillno desc"
    sql = sql + " limit :limit  offset :offset"
    param["limit"] = head.limit
    param["offset"] = head.offset
    data = db.execute(text(sql), param).fetchall()

    info_dict = {}
    info_dict['total'] = count
    info_dict['pageIndex'] = head.pageIndex
    info_dict['pageSize'] = head.pageSize

    # details 明细列表
    param = {}
    sql_3 = " select b.fline,b.fmaterialno,b.fbalqty,b.fpdqty,b.flosqty,b.fprocdate,b.fpduser,b.fpddate"
    sql_3 = sql_3 + " from lims.m_tc_check_head a,lims.m_tc_check_detail b where a.fbillno=b.fbillno and fbilltype=2"
    if head.fsiteNo != "":
        sql_3 = sql_3 + " and a.fsite_no = :fsiteNo"
        param["fsiteNo"] = head.fsiteNo
    if head.fbillno != "":
        sql_3 = sql_3 + " and a.fbillno like concat('%',:fbillno,'%')"
        param["fbillno"] = head.fbillno
    if head.fpduser != "":
        sql_3 = sql_3 + " and a.fpduser = :fpduser "
        param["fpduser"] = head.fpduser
    if head.fstockno != "":
        sql_3 = sql_3 + " and a.fstockno = :fstockno "
        param["fstockno"] = head.fstockno
    if head.fbilltype != "":
        sql_3 = sql_3 + " and a.fbilltype = :fbilltype "
        param["fbilltype"] = head.fbilltype
    if head.fbillstate != "":
        sql_3 = sql_3 + " and a.fbillstate = :fbillstate "
        param["fbillstate"] = head.fbillstate
    if (head.ftakedateStart != "") and (head.ftakedateEnd != ""):
        sql_3 = sql_3 + " and to_char(a.fpddate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = head.ftakedateStart
        param["ftakedateEnd"] = head.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['fpduser'] = data[key][2]
        d1['fpddate'] = data[key][3]
        d1['fstockno'] = data[key][4]
        d1['fitemcount'] = data[key][5]
        d1['fpiececount'] = data[key][6]
        d1['ftotalamt'] = data[key][7]
        d1['fexplanation'] = data[key][8]
        d1['fupddate'] = data[key][9]
        d1['foperator'] = data[key][10]
        d1['fcheckdate'] = data[key][11]
        d1['fcheckuser'] = data[key][12]
        d1['fbillstate'] = data[key][13]
        d1['fbilltype'] = data[key][14]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['fmaterialno'] = data_detail[j1][2]
                d2['fbalqty'] = data_detail[j1][3]
                d2['fpdqty'] = data_detail[j1][4]
                d2['flosqty'] = data_detail[j1][5]
                d2['fprocdate'] = data_detail[j1][6]
                d2['fpduser'] = data_detail[j1][7]
                d2['fpddate'] = data_detail[j1][8]
                detail_list.append(d2)

        d1["details"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": head.limit, "page": head.pageIndex,
                 "pages": (count + head.pageSize - 1) // head.pageSize,
                 "total": count}
    return BaseResponse.success(data=info_dict)


# class

class DetailItem(BaseModel):
    fline: int = Field(..., description="行号")
    fmaterialno: str = Field(..., description="物料编号")
    fbalqty: float = Field(..., description="账面数量")
    fpdqty: float = Field(..., description="盘点数量")
    flosqty: float = Field(..., description="盈亏数量")
    ftotamt: float = Field(0, description="总金额")
    fprocdate: str = Field(..., description="处理日期")
    fpduser: int = Field(..., description="盘点用户ID")
    fpddate: str = Field(..., description="盘点日期")


class HeadCreate(BaseModel):
    fbillno: str = Field(..., description="单据编号")
    fsiteNo: str = Field(..., description="站点编号")
    fpduser: int = Field(..., description="盘点用户ID")
    fpddate: str = Field(..., description="盘点日期")
    fbilltype: int = Field(..., description="单据类型")
    fstockno: str = Field(..., description="仓库编号")
    fexplanation: Optional[str] = Field("", description="备注说明")
    details: List[DetailItem] = Field(..., description="明细列表")


@check_head_router.post("/create", summary="耗材盘点新增")
def create_check_head(head: HeadCreate, db: Session = Depends(get_db)):
    try:
        sqllist = []
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        d1 = {}
        detail_list = []
        with db.begin():
            # 新增明细表
            if head.details is not None:
                for data in head.details:
                    sql = "insert into lims.m_tc_check_detail(fbillno,fline,fmaterialno,fbalqty,fpdqty,flosqty,fprocdate,fpduser,fpddate)"
                    sql = sql + " values(:fbillno , :fline , :fmaterialno , :fbalqty , :fpdqty , :flosqty , :fprocdate , :fpduser ,:fpddate )"
                    db.execute(text(sql), {
                        "fbillno": head.fbillno,
                        "fline": data.fline,
                        "fmaterialno": data.fmaterialno,
                        "fbalqty": data.fbalqty,
                        "fpdqty": data.fpdqty,
                        "flosqty": data.flosqty,
                        "fprocdate": data.fprocdate,
                        "fpduser": data.fpduser,
                        "fpddate": data.fpddate,
                    })
                    fitemcount = fitemcount + 1
                    fpiececount = float(fpiececount) + float(data.fpdqty)
                    ftotalamt = float(ftotalamt) + float(data.ftotamt)
                    # 返回明细
                    d2 = {}
                    d2['fbillno'] = head.fbillno
                    d2['fline'] = data.fline
                    d2['fmaterialno'] = data.fmaterialno
                    # d2['fbuyprice'] = float(data['fbuyprice'])
                    d2['fbalqty'] = float(data.fbalqty)
                    d2['fpdqty'] = float(data.fpdqty)
                    d2['flosqty'] = float(data.flosqty)
                    d2['ftotamt'] = float(data.ftotamt)
                    d2['fprocdate'] = data.fprocdate
                    d2['fpduser'] = float(data.fpduser)
                    d2['fpddate'] = data.fpddate
                    detail_list.append(d2)
            sql = "insert into lims.m_tc_check_head(fbillno,fsite_no,fpduser,fpddate ,fstockno,fitemcount,fpiececount,ftotalamt ,fexplanation,fupddate,foperator,fbillstate,fbilltype)"
            sql = sql + " values(:fbillno,:fsite_no , :fpduser ,:fpddate ,:fstockno,:fitemcount ,:fpiececount ,:ftotalamt ,:fexplanation ,now(),:fpduser ,1,:fbilltype )"
            db.execute(text(sql), {
                "fbillno": head.fbillno,
                "fsite_no": head.fsiteNo,
                "fpduser": head.fpduser,
                "fpddate": head.fpddate,
                "fstockno": head.fstockno,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "fexplanation": head.fexplanation,
                "fbilltype": head.fbilltype
            })
            # 返回
            d1['fbillno'] = head.fbillno
            d1['fsiteno'] = head.fsiteNo
            d1['fpduser'] = head.fpduser
            d1['fpddate'] = head.fpddate
            d1['fstockno'] = head.fstockno
            d1['fitemcount'] = fitemcount
            d1['fpiececount'] = fpiececount
            d1['ftotalamt'] = float(ftotalamt)
            d1['fsrcbillno'] = ""
            d1['fexplanation'] = ""
            d1['fupddate'] = head.fpddate
            d1['foperator'] = head.fpduser
            d1['fcheckdate'] = None
            d1['fcheckuser'] = None
            d1['fbillstate'] = 1
            d1['fbilltype'] = 1
            d1["details"] = detail_list
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="未知错误")


@check_head_router.post("/update", summary="耗材盘点修改")
def update_check_head(head: HeadCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = head.fbillno
        with db.begin():
            sql = "delete from lims.m_tc_check_detail where fbillno='" + fbillno + "'"
            # 新增明细表
            if head.details is not None:
                for data in head.details:
                    sql = "insert into lims.m_tc_check_detail(fbillno,fline,fmaterialno,fbalqty,fpdqty,flosqty,fprocdate,fpduser,fpddate)"
                    sql = sql + " values(:fbillno , :fline , :fmaterialno , :fbalqty , :fpdqty , :flosqty , :fprocdate , :fpduser ,:fpddate )"
                    db.execute(text(sql), {
                        "fbillno": head.fbillno,
                        "fline": data.fline,
                        "fmaterialno": data.fmaterialno,
                        "fbalqty": data.fbalqty,
                        "fpdqty": data.fpdqty,
                        "flosqty": data.flosqty,
                        "fprocdate": data.fprocdate,
                        "fpduser": data.fpduser,
                        "fpddate": data.fpddate,
                    })
                    fitemcount = fitemcount + 1
                    fpiececount = float(fpiececount) + float(data.fpdqty)
                    ftotalamt = float(ftotalamt) + float(data.ftotamt)
            sql = "update lims.m_tc_check_head "
            sql = sql + " set fexplanation= :fexplanation ,fitemcount=:fitemcount ,fpiececount= :fpiececount ,ftotalamt= :ftotalamt ,fupddate=now(),foperator= :user_id "
            sql = sql + " where fbillno= :fbillno "
            db.execute(text(sql), {
                "fexplanation": head.fexplanation,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "user_id": user_id,
                "fbillno": head.fbillno
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@check_head_router.post("/delete/{fbillno}", summary="删除盘点记录")
def delete_check(fbillno: str = Path(description="单号"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select count(1) from lims.m_tc_check_head where fbillno= :id and fbillstate>1"
            count = db.execute(text(sql), {"id": fbillno}).scalar()
            if count > 0:
                return BaseResponse.error(message="该订单不能删除")

            sql = "delete from lims.m_tc_check_detail where fbillno= :id"
            db.execute(text(sql), {"id": fbillno})

            sql = "delete from lims.m_tc_check_head where fbillno= :id"
            db.execute(text(sql), {"id": fbillno})
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
